Importing an Oracle Data Source

The minimum requirements for OpenCities Map to extract (import) spatial data from tables in Oracle Spatial or Locator are:

Tables can have only one geometry column (type: SDO_GEOMETRY).
Geometry data in a table must be restricted to only one geometry type (SDO_GTYPE).
To determine if a table contains more than one geometry type, use the following query:
```
SELECT 
t.<geometry column>.SDO_GTYPE, 
COUNT(*) 
FROM <TABLE NAME>T 
GROUP BY T<geometry column>.SDO_GTYPE;
```
If the query returns only one column, the table meets the one geometry type requirement for OpenCities Map.
Geometry type support is restricted to point, multi point, curve, multi curve, polygon and multi polygon types.
The geometry column needs to have a spatial index, with the geometry type explicitly defined.

The following is an example of a CREATE TABLE statement:

CREATE TABLE "PARCELS" 
(Owner VARCHAR2(20),
Market_Value VARCHAR2(10),
Parcel_ID NUMBER PRIMARY KEY,
Geometry MDSYS.SDO_GEOMETRY);

The following is an example of a CREATE INDEX statement with an explicit geometry type:

CREATE INDEX PARCELS_SIDX ON PARCELS(Geometry)
 INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2
   PARAMETERS ('layer_gtype=POINT');

Note: The naming convention <TABLE NAME>_SIDX is used for clarity, where SIDX is the acronym for "spatial data index".

Note: Valid layer_gtype entries are: POINT, LINE, CURVE, POLYGON, MULTIPOINT, MULTILINE, MULTIVURVE, and MULTIPOLYGON. Because of the limitations in dealing with polygons with holes in the DGN format, the layer_gtype to use for spatial tables that could include polygons with holes is MULTIPOLYGON (not POLYGON).

To be able to create a spatial index on a table it needs to be registered in the appropriate Oracle metadata view (typically USER_SDO_GEOM_METADATA).

The following is an example of the entry used to be able to successfully execute the CREATE INDEX statement listed above:

INSERT INTO USER_SDO_GEOM_METADATA
  (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES
  ('PARCELS', 'Geometry',
   SDO_DIM_ARRAY
    (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
    SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
  8307);

An alternate example would be:

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('PARCLES', 'Geometry',
MDSYS.SDO_DIM_ARRAY
(MDSYS.SDO_DIM_ELEMENT('X', 2197290.78478466, 2401264.08333616, 0.000000050), 
MDSYS.SDO_DIM_ELEMENT('Y', 703310.077261334, 911592.401111043, 0.000000050)),
NULL);

Any table with spatial data in Oracle that abides to the requirements listed in this section, is available for import into OpenCities Map using the Add Oracle (read-only) option in the Map Imports/Exports dialog.